﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
namespace DAO
{
    public class DIEMDAO
    {
        QUANLY_DIEMDataContext db = new QUANLY_DIEMDataContext();
        NAMHOCDAO namdao = new NAMHOCDAO();
        //Load danh sach điểm
        public DataTable LoadDiem(int malop, int mahk, int mamon)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("mahs");
                dt.Columns.Add("tenhs");
                dt.Columns.Add("mieng1");
                dt.Columns.Add("mieng2");
                dt.Columns.Add("mieng3");
                dt.Columns.Add("p15p1");
                dt.Columns.Add("p15p2");
                dt.Columns.Add("p15p3");
                dt.Columns.Add("p15p4");
                dt.Columns.Add("p15p5");
                dt.Columns.Add("p1t1");
                dt.Columns.Add("p1t2");
                dt.Columns.Add("p1t3");
                dt.Columns.Add("p1t4");
                dt.Columns.Add("p1t5");
                dt.Columns.Add("thi");
                dt.Columns.Add("mdmieng1");
                dt.Columns.Add("mdmieng2");
                dt.Columns.Add("mdmieng3");
                dt.Columns.Add("md15p1");
                dt.Columns.Add("md15p2");
                dt.Columns.Add("md15p3");
                dt.Columns.Add("md15p4");
                dt.Columns.Add("md15p5");
                dt.Columns.Add("md1t1");
                dt.Columns.Add("md1t2");
                dt.Columns.Add("md1t3");
                dt.Columns.Add("md1t4");
                dt.Columns.Add("md1t5");
                dt.Columns.Add("mdthi");
                int namhoc = namdao.LoadNamHocHienTai().MANAMHOC;
                var dshs = from c in db.CHITIETHOCSINHs
                           join l in db.LOPs
                           on c.MALOP equals l.MALOP
                           where c.MALOP == malop && c.MANAMHOC == namhoc
                           select new { c.MAHS };
                DataRow rd;
                foreach(var r in dshs)
                {
                    rd = dt.NewRow();
                    rd["mahs"] = r.MAHS;
                    rd["tenhs"] = db.HOCSINHs.FirstOrDefault<HOCSINH>(p=>p.MAHS==r.MAHS).TENHS;
                    var dsdm = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == namhoc && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var ds15 = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == namhoc && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var ds1 = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == namhoc && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var dsthi = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == namhoc && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    int i=2;
                    foreach(var p in dsdm)
                    {
                        rd[i] = string.Format("{0:0.0}", p.DIEM1);
                        rd[i + 14] = p.STT;
                        i++;
                    }
                    int j = 5;
                    foreach (var p in ds15)
                    {
                        rd[j] = string.Format("{0:0.0}", p.DIEM1);
                        rd[j + 14] = p.STT;
                        j++;
                    }
                    int k = 10;
                    foreach (var p in ds1)
                    {
                        rd[k] = string.Format("{0:0.0}", p.DIEM1);
                        rd[k + 14] = p.STT;
                        k++;
                    }
                    int t = 15;
                    foreach (var p in dsthi)
                    {
                        rd[t] = string.Format("{0:0.0}", p.DIEM1);
                        rd[t + 14] = p.STT;
                        t++;
                    }
                    dt.Rows.Add(rd);
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
        //thêm điểm
        public int ThemDiem(DIEMDTO diem)
        {
            try
            {
                DIEM d = new DIEM();
                d.MAHS = diem.MAHS;
                d.MAHK=diem.MAHK;
                d.MALCD=diem.MALCD;
                d.MAMH=diem.MAMH;
                d.MANAMHOC=namdao.LoadNamHocHienTai().MANAMHOC;
                d.DIEM1 =diem.DIEM;
                db.DIEMs.InsertOnSubmit(d);
                db.SubmitChanges();
                return db.DIEMs.OrderByDescending(p => p.STT).FirstOrDefault<DIEM>().STT;
            }
            catch
            {
                return 0;
            }
        }
        //xóa điểm
        public int XoaDiem(int madiem)
        {
            try
            {
                DIEM d = db.DIEMs.SingleOrDefault<DIEM>(p => p.STT == madiem);
                db.DIEMs.DeleteOnSubmit(d);
                db.SubmitChanges();
                return 1;
            }
            catch
            {
                return 0;
            }
        }
        //Tim Kiếm hoc sinh theo ten
        public DataTable LoadDiemTheoTen(int mahk, int mamon, string tenhocsinh)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("mahs");
                dt.Columns.Add("tenhs");
                dt.Columns.Add("mieng1");
                dt.Columns.Add("mieng2");
                dt.Columns.Add("mieng3");
                dt.Columns.Add("p15p1");
                dt.Columns.Add("p15p2");
                dt.Columns.Add("p15p3");
                dt.Columns.Add("p15p4");
                dt.Columns.Add("p15p5");
                dt.Columns.Add("p1t1");
                dt.Columns.Add("p1t2");
                dt.Columns.Add("p1t3");
                dt.Columns.Add("p1t4");
                dt.Columns.Add("p1t5");
                dt.Columns.Add("thi");
                dt.Columns.Add("mdmieng1");
                dt.Columns.Add("mdmieng2");
                dt.Columns.Add("mdmieng3");
                dt.Columns.Add("md15p1");
                dt.Columns.Add("md15p2");
                dt.Columns.Add("md15p3");
                dt.Columns.Add("md15p4");
                dt.Columns.Add("md15p5");
                dt.Columns.Add("md1t1");
                dt.Columns.Add("md1t2");
                dt.Columns.Add("md1t3");
                dt.Columns.Add("md1t4");
                dt.Columns.Add("md1t5");
                dt.Columns.Add("mdthi");
                int namhoc = namdao.LoadNamHocHienTai().MANAMHOC;
                var dshs = from c in db.HOCSINHs
                           where c.TENHS.Contains(tenhocsinh) && c.TINHTRANG==1
                           select new { c.MAHS };
                DataRow rd;
                foreach (var r in dshs)
                {
                    rd = dt.NewRow();
                    rd["mahs"] = r.MAHS;
                    rd["tenhs"] = db.HOCSINHs.FirstOrDefault<HOCSINH>(p => p.MAHS == r.MAHS).TENHS;
                    var dsdm = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == namhoc && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var ds15 = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == namhoc && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var ds1 = from p in db.DIEMs
                              where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == namhoc && p.MAMH == mamon
                              select new { p.STT, p.DIEM1 };
                    var dsthi = from p in db.DIEMs
                                where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == namhoc && p.MAMH == mamon
                                select new { p.STT, p.DIEM1 };
                    int i = 2;
                    foreach (var p in dsdm)
                    {
                        rd[i] = string.Format("{0:0.0}", p.DIEM1);
                        rd[i + 14] = p.STT;
                        i++;
                    }
                    int j = 5;
                    foreach (var p in ds15)
                    {
                        rd[j] = string.Format("{0:0.0}", p.DIEM1);
                        rd[j + 14] = p.STT;
                        j++;
                    }
                    int k = 10;
                    foreach (var p in ds1)
                    {
                        rd[k] = string.Format("{0:0.0}", p.DIEM1);
                        rd[k + 14] = p.STT;
                        k++;
                    }
                    int t = 15;
                    foreach (var p in dsthi)
                    {
                        rd[t] = string.Format("{0:0.0}", p.DIEM1);
                        rd[t + 14] = p.STT;
                        t++;
                    }
                    dt.Rows.Add(rd);
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
        //Xem Diem hoc sinh
        public DataTable LoadDiemHocSinh(int mahk, int mahs, int nam)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("mon");
                dt.Columns.Add("hk");
                dt.Columns.Add("nam");
                dt.Columns.Add("mieng1");
                dt.Columns.Add("mieng2");
                dt.Columns.Add("mieng3");
                dt.Columns.Add("p15p1");
                dt.Columns.Add("p15p2");
                dt.Columns.Add("p15p3");
                dt.Columns.Add("p15p4");
                dt.Columns.Add("p15p5");
                dt.Columns.Add("p1t1");
                dt.Columns.Add("p1t2");
                dt.Columns.Add("p1t3");
                dt.Columns.Add("p1t4");
                dt.Columns.Add("p1t5");
                dt.Columns.Add("thi");
                dt.Columns.Add("tbmon");
                var dsmon = db.MONHOCs.Select(p => new { p.MAMH, p.TENMH});
                DataRow rd;
                NAMHOC nh = db.NAMHOCs.FirstOrDefault<NAMHOC>(p => p.MANAMHOC == nam);
                foreach (var r in dsmon)
                {
                    double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                    int dem = 0;
                    rd = dt.NewRow();
                    rd["mon"] = r.TENMH;
                    rd["hk"] = db.HOCKies.FirstOrDefault<HOCKY>(p => p.MAHK == mahk).TENHK;
                    rd["nam"] = nh.NAMBATDAU + " - " + nh.NAMKETTHUC;
                    var dsdm = from p in db.DIEMs
                               where p.MAHS == mahs && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == nam && p.MAMH == r.MAMH
                               select new { p.STT, p.DIEM1 };
                    var ds15 = from p in db.DIEMs
                               where p.MAHS == mahs && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == nam && p.MAMH == r.MAMH
                               select new { p.STT, p.DIEM1 };
                    var ds1 = from p in db.DIEMs
                              where p.MAHS == mahs && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == nam && p.MAMH == r.MAMH
                              select new { p.STT, p.DIEM1 };
                    var dsthi = from p in db.DIEMs
                                where p.MAHS == mahs && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == nam && p.MAMH == r.MAMH
                                select new { p.STT, p.DIEM1 };
                    int i = 3;
                    foreach (var p in dsdm)
                    {
                        rd[i] = string.Format("{0:0.0}", p.DIEM1);
                        i++;
                        dem++;
                        diemhe1 += p.DIEM1;
                    }
                    int j = 6;
                    foreach (var p in ds15)
                    {
                        rd[j] = string.Format("{0:0.0}", p.DIEM1);
                        j++;
                        dem++;
                        diemhe1 += p.DIEM1;
                    }
                    int k = 11;
                    foreach (var p in ds1)
                    {
                        rd[k] = string.Format("{0:0.0}", p.DIEM1);
                        k++;
                        dem += 2;
                        diemhe2 += p.DIEM1 * 2;
                    }
                    int t = 16;
                    foreach (var p in dsthi)
                    {
                        rd[t] = string.Format("{0:0.0}", p.DIEM1);
                        t++;
                        dem += 3;
                        diemhe3 += p.DIEM1 * 3;
                    }
                    double tb = 0;
                    if(dem != 0)
                    {
                        tb = (diemhe1 + diemhe2 + diemhe3) / dem;
                    }
                    rd["tbmon"] = string.Format("{0:0.0}",tb);
                    dt.Rows.Add(rd);
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
        //Tinh Diem hoc sinh
        public DataTable DanhSachXepLoaiHocSinh(int mahk, int malop)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("tenhs");
                dt.Columns.Add("dtb");
                dt.Columns.Add("loai");
                var dsmon = db.MONHOCs.Select(p => new { p.MAMH, p.TENMH });
                int namhoc = namdao.LoadNamHocHienTai().MANAMHOC;
                float yeu = float.Parse(db.THAMSOs.SingleOrDefault(p => p.MATS == 1).GIATRI);
                float trungbinh = float.Parse(db.THAMSOs.SingleOrDefault(p => p.MATS == 2).GIATRI);
                float kha = float.Parse(db.THAMSOs.SingleOrDefault(p => p.MATS == 3).GIATRI);
                float gioi = float.Parse(db.THAMSOs.SingleOrDefault(p => p.MATS == 4).GIATRI);
                float xuatsac = float.Parse(db.THAMSOs.SingleOrDefault(p => p.MATS == 5).GIATRI);
                var dshs = from h in db.HOCSINHs
                           join c in db.CHITIETHOCSINHs
                           on h.MAHS equals c.MAHS
                           where c.MALOP == malop && c.MANAMHOC == namhoc && h.TINHTRANG==1
                           select new { c.MAHS, h.TENHS };
                DataRow rd;
                foreach (var hs in dshs)
                {
                    rd = dt.NewRow();
                    rd["tenhs"] = hs.TENHS;
                    int demmon = 0;
                    double tongdiem = 0;
                    foreach (var r in dsmon)
                    {
                        demmon++;
                        double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                        int dem = 0;

                        var dsdm = from p in db.DIEMs
                                   where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == namhoc && p.MAMH == r.MAMH
                                   select new { p.STT, p.DIEM1 };
                        var ds15 = from p in db.DIEMs
                                   where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == namhoc && p.MAMH == r.MAMH
                                   select new { p.STT, p.DIEM1 };
                        var ds1 = from p in db.DIEMs
                                  where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == namhoc && p.MAMH == r.MAMH
                                  select new { p.STT, p.DIEM1 };
                        var dsthi = from p in db.DIEMs
                                    where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == namhoc && p.MAMH == r.MAMH
                                    select new { p.STT, p.DIEM1 };

                        foreach (var p in dsdm)
                        {
                            dem++;
                            diemhe1 += p.DIEM1;
                        }

                        foreach (var p in ds15)
                        {
                            dem++;
                            diemhe1 += p.DIEM1;
                        }

                        foreach (var p in ds1)
                        {
                            dem += 2;
                            diemhe2 += p.DIEM1 * 2;
                        }
                        foreach (var p in dsthi)
                        {
                            dem += 3;
                            diemhe3 += p.DIEM1 * 3;
                        }
                        double tb = 0;
                        if (dem != 0)
                        {
                            tb = (diemhe1 + diemhe2 + diemhe3) / dem;
                        }
                        tongdiem += tb;
                    }
                    double dtb = 0;
                    if(demmon!=0)
                    {
                        dtb = tongdiem/demmon;
                    }
                    rd["dtb"] = string.Format("{0:0.0}", dtb);
                    string loai = "";
                    if (dtb < yeu)
                    {
                        loai = "Kém";
                    }
                    if (dtb >= yeu && dtb < trungbinh)
                    {
                        loai = "Yếu";
                    }
                    if (dtb >= trungbinh && dtb < kha)
                    {
                        loai = "Trung Bình";
                    }
                    if (dtb >= kha && dtb < gioi)
                    {
                        loai = "Khá";
                    }
                    if (dtb >= gioi && dtb < xuatsac)
                    {
                        loai = "Giỏi";
                    }
                    if (dtb >= xuatsac)
                    {
                        loai = "Xuất Sắc";
                    }
                    rd["loai"] = loai;
                    dt.Rows.Add(rd);
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
        //Load danh sach điểm theo nam
        public DataTable LoadDiemTheoLopTheoNam(int malop, int mahk, int mamon, int nam)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("lop");
                dt.Columns.Add("tenhs");
                dt.Columns.Add("mieng1");
                dt.Columns.Add("mieng2");
                dt.Columns.Add("mieng3");
                dt.Columns.Add("p15p1");
                dt.Columns.Add("p15p2");
                dt.Columns.Add("p15p3");
                dt.Columns.Add("p15p4");
                dt.Columns.Add("p15p5");
                dt.Columns.Add("p1t1");
                dt.Columns.Add("p1t2");
                dt.Columns.Add("p1t3");
                dt.Columns.Add("p1t4");
                dt.Columns.Add("p1t5");
                dt.Columns.Add("thi");
                dt.Columns.Add("dtb");
                var dshs = from c in db.CHITIETHOCSINHs
                           join l in db.LOPs
                           on c.MALOP equals l.MALOP
                           where c.MALOP == malop && c.MANAMHOC == nam
                           select new { c.MAHS, l.TENLOP };
                DataRow rd;
                foreach (var r in dshs)
                {
                    rd = dt.NewRow();
                    rd["lop"] = r.TENLOP;
                    rd["tenhs"] = db.HOCSINHs.FirstOrDefault<HOCSINH>(p => p.MAHS == r.MAHS).TENHS;
                    var dsdm = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == nam && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var ds15 = from p in db.DIEMs
                               where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == nam && p.MAMH == mamon
                               select new { p.STT, p.DIEM1 };
                    var ds1 = from p in db.DIEMs
                              where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == nam && p.MAMH == mamon
                              select new { p.STT, p.DIEM1 };
                    var dsthi = from p in db.DIEMs
                                where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == nam && p.MAMH == mamon
                                select new { p.STT, p.DIEM1 };
                    int i = 2;
                    int dem = 0;
                    double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                    foreach (var p in dsdm)
                    {
                        rd[i] = string.Format("{0:0.0}", p.DIEM1);
                        i++;
                        dem++;
                        diemhe1+=p.DIEM1;
                    }
                    int j = 5;
                    foreach (var p in ds15)
                    {
                        rd[j] = string.Format("{0:0.0}", p.DIEM1);;
                        j++;
                        dem++;
                        diemhe1+=p.DIEM1;
                    }
                    int k = 10;
                    foreach (var p in ds1)
                    {
                        rd[k] = string.Format("{0:0.0}", p.DIEM1);
                        k++;
                        dem += 2;
                        diemhe1+=p.DIEM1*2;
                    }
                    int t = 15;
                    foreach (var p in dsthi)
                    {
                        rd[t] = string.Format("{0:0.0}", p.DIEM1);
                        t++;
                        dem += 3;
                        diemhe1+=p.DIEM1*3;
                    }
                    double dtb = 0;
                    if (dem != 0)
                    {
                        dtb= (diemhe1+diemhe2+diemhe3) / dem;
                    }
                    rd["dtb"] = string.Format("{0:0.0}", dtb);
                    dt.Rows.Add(rd);
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }

        //Load danh sach điểm theo Khoi
        public DataTable LoadDiemTheoLopTheoKhoi(int makhoi, int mahk, int mamon, int nam)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("khoi");
                dt.Columns.Add("lop");
                dt.Columns.Add("tenhs");
                dt.Columns.Add("mieng1");
                dt.Columns.Add("mieng2");
                dt.Columns.Add("mieng3");
                dt.Columns.Add("p15p1");
                dt.Columns.Add("p15p2");
                dt.Columns.Add("p15p3");
                dt.Columns.Add("p15p4");
                dt.Columns.Add("p15p5");
                dt.Columns.Add("p1t1");
                dt.Columns.Add("p1t2");
                dt.Columns.Add("p1t3");
                dt.Columns.Add("p1t4");
                dt.Columns.Add("p1t5");
                dt.Columns.Add("thi");
                dt.Columns.Add("dtb");
                var dskhoi = db.KHOIs.Where(p=>p.MAKH==makhoi);
                DataRow rd;
                foreach (var kh in dskhoi)
                {
                    rd = dt.NewRow();
                    rd["khoi"] = kh.TENKH;
                    var dshs = from c in db.CHITIETHOCSINHs
                               join l in db.LOPs
                               on c.MALOP equals l.MALOP
                               where c.MANAMHOC == nam && l.MAKH == kh.MAKH
                               select new { c.MAHS, l.TENLOP };

                    foreach (var r in dshs)
                    {
                        rd = dt.NewRow();
                        rd["khoi"] = kh.TENKH;
                        rd["lop"] = r.TENLOP;
                        rd["tenhs"] = db.HOCSINHs.FirstOrDefault<HOCSINH>(p => p.MAHS == r.MAHS).TENHS;
                        var dsdm = from p in db.DIEMs
                                   where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == nam && p.MAMH == mamon
                                   select new { p.STT, p.DIEM1 };
                        var ds15 = from p in db.DIEMs
                                   where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == nam && p.MAMH == mamon
                                   select new { p.STT, p.DIEM1 };
                        var ds1 = from p in db.DIEMs
                                  where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == nam && p.MAMH == mamon
                                  select new { p.STT, p.DIEM1 };
                        var dsthi = from p in db.DIEMs
                                    where p.MAHS == r.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == nam && p.MAMH == mamon
                                    select new { p.STT, p.DIEM1 };
                        int i = 3;
                        int dem = 0;
                        double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                        foreach (var p in dsdm)
                        {
                            rd[i] = string.Format("{0:0.0}", p.DIEM1);
                            i++;
                            dem++;
                            diemhe1 += p.DIEM1;
                        }
                        int j = 6;
                        foreach (var p in ds15)
                        {
                            rd[j] = string.Format("{0:0.0}", p.DIEM1); ;
                            j++;
                            dem++;
                            diemhe1 += p.DIEM1;
                        }
                        int k = 11;
                        foreach (var p in ds1)
                        {
                            rd[k] = string.Format("{0:0.0}", p.DIEM1);
                            k++;
                            dem += 2;
                            diemhe1 += p.DIEM1 * 2;
                        }
                        int t = 16;
                        foreach (var p in dsthi)
                        {
                            rd[t] = string.Format("{0:0.0}", p.DIEM1);
                            t++;
                            dem += 3;
                            diemhe1 += p.DIEM1 * 3;
                        }
                        double dtb = 0;
                        if (dem != 0)
                        {
                            dtb = (diemhe1 + diemhe2 + diemhe3) / dem;
                        }
                        rd["dtb"] = string.Format("{0:0.0}", dtb);
                        dt.Rows.Add(rd);
                    }                    
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }

        //Thong Ke cac mon theo lop
        public DataTable ThongKeTatCaMonTheoLop(int mahk, int malop, int manam)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("tenhs");
                dt.Columns.Add("Toan");
                dt.Columns.Add("Ly");
                dt.Columns.Add("Hoa");
                dt.Columns.Add("Sinh");
                dt.Columns.Add("Su");
                dt.Columns.Add("Dia");
                dt.Columns.Add("Anh");
                dt.Columns.Add("CD");
                dt.Columns.Add("CN");
                dt.Columns.Add("TD");
                dt.Columns.Add("Van");
                dt.Columns.Add("dtb");
                var dsmon = db.MONHOCs.Select(p => new { p.MAMH, p.TENMH });
                
                var dshs = from h in db.HOCSINHs
                           join c in db.CHITIETHOCSINHs
                           on h.MAHS equals c.MAHS
                           where c.MALOP == malop && c.MANAMHOC == manam && h.TINHTRANG == 1
                           select new { c.MAHS, h.TENHS };
                DataRow rd;
                foreach (var hs in dshs)
                {
                    rd = dt.NewRow();
                    rd["tenhs"] = hs.TENHS;
                    int demmon = 0;
                    double tongdiem = 0;
                    int i = 1;
                    foreach (var r in dsmon)
                    {
                        demmon++;
                        double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                        int dem = 0;

                        var dsdm = from p in db.DIEMs
                                   where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                   select new { p.STT, p.DIEM1 };
                        var ds15 = from p in db.DIEMs
                                   where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                   select new { p.STT, p.DIEM1 };
                        var ds1 = from p in db.DIEMs
                                  where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                  select new { p.STT, p.DIEM1 };
                        var dsthi = from p in db.DIEMs
                                    where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                    select new { p.STT, p.DIEM1 };

                        foreach (var p in dsdm)
                        {
                            dem++;
                            diemhe1 += p.DIEM1;
                        }

                        foreach (var p in ds15)
                        {
                            dem++;
                            diemhe1 += p.DIEM1;
                        }

                        foreach (var p in ds1)
                        {
                            dem += 2;
                            diemhe2 += p.DIEM1 * 2;
                        }
                        foreach (var p in dsthi)
                        {
                            dem += 3;
                            diemhe3 += p.DIEM1 * 3;
                        }
                        double tb = 0;
                        if (dem != 0)
                        {
                            tb = (diemhe1 + diemhe2 + diemhe3) / dem;
                        }
                        tongdiem += tb;
                        rd[i++] = string.Format("{0:0.0}", tb);
                    }
                    double dtb = 0;
                    if (demmon != 0)
                    {
                        dtb = tongdiem / demmon;
                    }
                    rd["dtb"] = string.Format("{0:0.0}", dtb);
                    
                    dt.Rows.Add(rd);
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
        //Thong Ke cac mon theo khoi
        public DataTable ThongKeTatCaMonTheoKhoi(int mahk, int makhoi, int manam)
        {
            try
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("tenhs");
                dt.Columns.Add("Toan");
                dt.Columns.Add("Ly");
                dt.Columns.Add("Hoa");
                dt.Columns.Add("Sinh");
                dt.Columns.Add("Su");
                dt.Columns.Add("Dia");
                dt.Columns.Add("Anh");
                dt.Columns.Add("CD");
                dt.Columns.Add("CN");
                dt.Columns.Add("TD");
                dt.Columns.Add("Van");
                dt.Columns.Add("dtb");
                dt.Columns.Add("lop");
                dt.Columns.Add("khoi");
                var dskhoi = db.KHOIs.Where(p=>p.MAKH==makhoi);
                DataRow rd;
                foreach (var kh in dskhoi)
                {       
                    var dsmon = db.MONHOCs.Select(p => new { p.MAMH, p.TENMH });
                    var dshs = from h in db.HOCSINHs
                               join c in db.CHITIETHOCSINHs on h.MAHS equals c.MAHS
                               join l in db.LOPs on c.MALOP  equals l.MALOP
                               where c.MANAMHOC == manam && h.TINHTRANG == 1 && l.MAKH == kh.MAKH
                               select new { c.MAHS, h.TENHS, l.TENLOP};
                    foreach (var hs in dshs)
                    {
                        rd = dt.NewRow();
                        rd["khoi"] = kh.TENKH;
                        rd["tenhs"] = hs.TENHS;
                        rd["lop"] = hs.TENLOP;
                        int demmon = 0;
                        double tongdiem = 0;
                        int i = 1;
                        foreach (var r in dsmon)
                        {
                            demmon++;
                            double diemhe1 = 0, diemhe2 = 0, diemhe3 = 0;
                            int dem = 0;

                            var dsdm = from p in db.DIEMs
                                       where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 1 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                       select new { p.STT, p.DIEM1 };
                            var ds15 = from p in db.DIEMs
                                       where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 2 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                       select new { p.STT, p.DIEM1 };
                            var ds1 = from p in db.DIEMs
                                      where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 3 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                      select new { p.STT, p.DIEM1 };
                            var dsthi = from p in db.DIEMs
                                        where p.MAHS == hs.MAHS && p.MAHK == mahk && p.MALCD == 4 && p.MANAMHOC == manam && p.MAMH == r.MAMH
                                        select new { p.STT, p.DIEM1 };

                            foreach (var p in dsdm)
                            {
                                dem++;
                                diemhe1 += p.DIEM1;
                            }

                            foreach (var p in ds15)
                            {
                                dem++;
                                diemhe1 += p.DIEM1;
                            }

                            foreach (var p in ds1)
                            {
                                dem += 2;
                                diemhe2 += p.DIEM1 * 2;
                            }
                            foreach (var p in dsthi)
                            {
                                dem += 3;
                                diemhe3 += p.DIEM1 * 3;
                            }
                            double tb = 0;
                            if (dem != 0)
                            {
                                tb = (diemhe1 + diemhe2 + diemhe3) / dem;
                            }
                            tongdiem += tb;
                            rd[i++] = string.Format("{0:0.0}", tb);
                        }
                        double dtb = 0;
                        if (demmon != 0)
                        {
                            dtb = tongdiem / demmon;
                        }
                        rd["dtb"] = string.Format("{0:0.0}", dtb);
                        dt.Rows.Add(rd);
                    } 
                }
                return dt;
            }
            catch
            {
                return null;
            }
        }
    }
}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           